The CCMS database is established and set as the active database.

CREATE DATABASE IF NOT EXISTS CCMS;
USE CCMS;

The primary system tables are created to store student, club, membership, event, participation, and announcement information.

CREATE TABLE students (
  student_id INT AUTO_INCREMENT,
  first_name VARCHAR(80),
  last_name  VARCHAR(80),
  email      VARCHAR(120) UNIQUE,
  join_date  DATE,
  PRIMARY KEY (student_id)
);

CREATE TABLE clubs (
  club_id     INT AUTO_INCREMENT,
  club_name   VARCHAR(120) UNIQUE,
  category    VARCHAR(60),
  founded_date DATE,
  PRIMARY KEY (club_id)
);

CREATE TABLE memberships (
  membership_id   INT AUTO_INCREMENT,
  student_id      INT,
  club_id         INT,
  membership_date DATE,
  role            VARCHAR(40),
  PRIMARY KEY (membership_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (club_id)    REFERENCES clubs(club_id)
);

CREATE TABLE events (
  event_id   INT AUTO_INCREMENT,
  club_id    INT,
  event_name VARCHAR(160),
  event_date DATETIME,
  venue      VARCHAR(160),
  capacity   INT,
  PRIMARY KEY (event_id),
  FOREIGN KEY (club_id) REFERENCES clubs(club_id)
);

CREATE TABLE participation (
  participation_id INT AUTO_INCREMENT,
  event_id         INT,
  student_id       INT,
  attended         TINYINT(1),
  checkin_time     DATETIME,
  PRIMARY KEY (participation_id),
  FOREIGN KEY (event_id)  REFERENCES events(event_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id)
);

CREATE TABLE announcements (
  announcement_id INT AUTO_INCREMENT,
  club_id         INT,
  title           VARCHAR(160),
  body            TEXT,
  sent_at         DATETIME,
  PRIMARY KEY (announcement_id),
  FOREIGN KEY (club_id) REFERENCES clubs(club_id)
);



CREATE TABLE club_audit_log (
  log_id     INT AUTO_INCREMENT PRIMARY KEY,
  club_id    INT,
  old_name   VARCHAR(120),
  new_name   VARCHAR(120),
  changed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Representative sample data is inserted for demonstration and testing purposes.

INSERT INTO students (first_name, last_name, email, join_date) VALUES
('Kelvin', 'Tavares', 'kelvin@example.com',          '2024-10-01'),
('Maria',  'Costa',   'maria.costa@example.com',     '2024-09-20'),
('Luca',   'Ricci',   'l.ricci@example.com',         '2024-09-15'),
('Aisha',  'Khan',    'aisha.khan@example.com',      '2024-10-10'),
('James',  'Brown',   'j.brown@example.com',         '2024-10-05');

INSERT INTO clubs (club_name, category, founded_date) VALUES
('Football Club',     'Sports',     '2022-01-10'),
('Coding Club',       'Technology', '2021-09-15'),
('Drama Society',     'Arts',       '2020-11-01'),
('Music Club',        'Culture',    '2019-05-22'),
('Photography Club',  'Media',      '2023-03-05');

INSERT INTO memberships (student_id, club_id, membership_date, role) VALUES
(1, 1, '2024-10-02', 'Player'),
(2, 2, '2024-09-22', 'Member'),
(3, 3, '2024-09-18', 'Actor'),
(4, 4, '2024-10-12', 'Singer'),
(5, 5, '2024-10-07', 'Member');

INSERT INTO events (club_id, event_name, event_date, venue, capacity) VALUES
(1, 'Match Day vs UCL',     '2025-01-20 14:00:00', 'Sports Hall',    50),
(2, 'Hackathon 2025',       '2025-02-10 09:30:00', 'Lab A201',       100),
(3, 'Winter Play',          '2024-12-05 19:00:00', 'Theatre Room',   80),
(4, 'Open Mic Night',       '2024-11-18 18:00:00', 'Music Studio',   60),
(5, 'Campus Photo Shoot',   '2025-03-01 10:00:00', 'Campus Grounds', 40);

INSERT INTO participation (event_id, student_id, attended, checkin_time) VALUES
(1, 1, 1, '2025-01-20 13:45:00'),
(2, 2, 1, '2025-02-10 09:10:00'),
(3, 3, 0, NULL),
(4, 4, 1, '2024-11-18 18:00:00'),
(5, 5, 1, '2025-03-01 10:05:00');

INSERT INTO announcements (club_id, title, body, sent_at) VALUES
(1, 'Training Change',     'Training will be on Thursday',           '2024-10-15 10:00:00'),
(2, 'New Course Available','Intro to Python workshops',              '2024-10-20 12:30:00'),
(3, 'Auditions',           'Auditions open next week',               '2024-10-18 15:00:00'),
(4, 'Music Night',         'Bring your instruments',                 '2024-11-10 09:00:00'),
(5, 'Photo Contest',       'Submit your best picture to win',        '2024-10-25 14:45:00');

A stored procedure is defined to insert new student records in a controlled manner.

DELIMITER $$

CREATE PROCEDURE add_student(
  IN p_first_name VARCHAR(80),
  IN p_last_name  VARCHAR(80),
  IN p_email      VARCHAR(120),
  IN p_join_date  DATE
)
BEGIN
  INSERT INTO students (first_name, last_name, email, join_date)
  VALUES (p_first_name, p_last_name, p_email, p_join_date);
END$$

DELIMITER ;

A trigger is introduced to automatically record any changes to club names.

DELIMITER $$

CREATE TRIGGER trg_club_name_update
AFTER UPDATE ON clubs
FOR EACH ROW
BEGIN
  IF OLD.club_name <> NEW.club_name THEN
    INSERT INTO club_audit_log (club_id, old_name, new_name)
    VALUES (OLD.club_id, OLD.club_name, NEW.club_name);
  END IF;
END$$

DELIMITER ;

Indexes are applied to improve query performance on frequently accessed fields.

CREATE INDEX idx_memberships_student
  ON memberships(student_id);

CREATE INDEX idx_memberships_club
  ON memberships(club_id);

CREATE INDEX idx_participation_event
  ON participation(event_id);

CREATE INDEX idx_participation_student
  ON participation(student_id);

A transaction block is provided as an example for testing controlled changes.


-- START TRANSACTION;
-- UPDATE memberships SET role = 'Coach' WHERE membership_id = 1;
-- COMMIT;
-- Or rollback if necessary:
-- ROLLBACK;

-- A foreign key validation test is provided to verify constraint enforcement.
-- Uncomment the following statement to verify behaviour:

-- INSERT INTO memberships (student_id, club_id, membership_date, role)
-- VALUES (999, 1, '2025-01-01', 'Member');
-- This insertion should be rejected because the referenced student does not exist.
